package com.mall.manager.service;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;

import javax.annotation.Resource;

import org.apache.log4j.Logger;

import com.mall.common.dao.MysqlDBManager;
import com.mysql.jdbc.Connection;
import com.mysql.jdbc.PreparedStatement;

/**
 * 查询地区name:code数据
 * @author tgy
 *
 */
@Resource
public class AreaMapService {
	
	public static Logger logger = Logger.getLogger(AreaMapService.class);
	
	private static Connection conn = null;
	private static PreparedStatement ps = null;
	private static ResultSet res = null;
	
	private static Map<String,Object> province = new HashMap<String,Object>();
	private static Map<String,Object> city = new HashMap<String,Object>();
	private static Map<String,Object> area = new HashMap<String,Object>();

	//查询省
	private static String psql = "select code,name from db_archive.province";
	//查询市
	private static String csql = "select ab.code,if(ab.name=1,ab.pname,concat(ab.pname,ab.cname)) as name,ab.provincecode from (select c.code,Locate(p.name,c.name) name,p.name as pname,c.name as cname,c.provincecode from db_archive.city c,db_archive.province p where p.code=c.provincecode)ab";
	//查询区
	private static String asql = "select code,concat(if(name=1,concat(pca.cname,pca.aname),concat(pca.pname,pca.cname,pca.aname))) as name,pca.cityCode from (select ac.code,locate(p.name,ac.cname) as name,p.name as pname,ac.cname as cname,ac.aname as aname,ac.cityCode from (select a.code,concat(c.name,a.name) name,c.name as cname,a.name as aname,c.provincecode,a.cityCode from db_archive.area a,db_archive.city c where a.citycode=c.code) as ac,db_archive.province p where p.code=ac.provincecode)pca";
	
	static{
		try{
			//加载省信息
			conn = MysqlDBManager.getConnection();
			ps = (PreparedStatement) conn.prepareStatement(psql);
			res = ps.executeQuery();
			while(res.next()){
				province.put(res.getString("name"),res.getString("code"));
			}
			//加载市信息
			ps = (PreparedStatement) conn.prepareStatement(csql);
			res = ps.executeQuery();
			while(res.next()){
				city.put(res.getString("name"),res.getString("code"));
			}
			//加载区信息
			ps = (PreparedStatement) conn.prepareStatement(asql);
			res = ps.executeQuery();
			while(res.next()){
				area.put(res.getString("name"),res.getString("code"));
			}
		}catch(Exception e){
			logger.info("查询地区出现异常："+e.getMessage());
		}finally{
			try {
				conn.close();
				ps.close();
				res.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	//地区map
	public static Map<String,Object> loadAreaMap(String str){
		if(str.equals("p")){
			return province;
		}
		if(str.equals("c")){
			return city;
		}
		return area;
	}
}
